package com.esm.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.esm.dao.SignDao;
import com.esm.utils.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


public class SignDaoImpl implements SignDao{

	@Override
	public boolean addStartTime(int uid, String start, String sgroup) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("INSERT INTO ems_sign (uid,startTime,sgroup) ");
		sql.append(" VALUES (?,?,?)");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setInt(1, uid);
			ps.setString(2, start);
			ps.setString(3, sgroup);
			int i=ps.executeUpdate();
			if(i!=0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean addLeaveTime(String endTime, String sgroup) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("UPDATE ems_sign ");
		sql.append(" SET endTime=?");
		sql.append(" WHERE sgroup=?");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setString(1, endTime);
			ps.setString(2, sgroup);
			int i=ps.executeUpdate();
			if(i!=0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public JSONObject searchSignTime(String sgroup) {
		JSONObject signTime=new JSONObject();
		StringBuffer sql=new StringBuffer("SELECT startTime,endTime ");
		sql.append(" FROM ems_sign");
		sql.append(" WHERE sgroup=?");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setString(1, sgroup);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				signTime.put("startTime", rs.getString("startTime"));
				signTime.put("endTime", rs.getString("endTime"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return signTime;
	}

	@Override
	public boolean changeState(String sgroup, String state) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("UPDATE ems_sign");
		sql.append(" SET state=?");
		sql.append(" WHERE sgroup=?");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setString(1, state);
			ps.setString(2, sgroup);
			int i=ps.executeUpdate();
			if(i!=0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}


/**
 * SignDaoImpl
 * 签到Dao层实现类
 * @author 魏长均
 *
 */

	@Override
	public JSONArray findEmpSign(String selectYear,int page,int pageSize) 
	{
		JSONArray empSign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, selectYear+"%");	
			pst.setInt(2, (page-1)*pageSize);	
			pst.setInt(3, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				empSign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return empSign;
	}

	@Override
	public int findEmpSignTotalRows(String selectYear) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" WHERE ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, selectYear+"%");
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public int findYrMthSignTotalRows(String selectYear, String selectMonth) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" WHERE ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			if(Integer.parseInt(selectMonth) < 10)
			{
				pst.setString(1, selectYear+"-"+0+selectMonth+"%");
			}
			else
			{
				pst.setString(1, selectYear+"-"+selectMonth+"%");
			}
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public JSONArray findYrMthEmpSign(String selectYear, String selectMonth, int page, int pageSize) 
	{
		JSONArray empYrMthSign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			if(Integer.parseInt(selectMonth) < 10)
			{
				pst.setString(1, selectYear+"-"+0+selectMonth+"%");
			}
			else
			{
				pst.setString(1, selectYear+"-"+selectMonth+"%");
			}
			pst.setInt(2, (page-1)*pageSize);	
			pst.setInt(3, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				empYrMthSign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return empYrMthSign;
	}

	@Override
	public JSONArray findYrMthDayEmpSign(String selectYear, String selectMonth, String selectDay, int page,
			int pageSize) 
	{
		JSONArray empYrMthDaySign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			if(Integer.parseInt(selectMonth) < 10)
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(1, selectYear+"-"+0+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(1, selectYear+"-"+0+selectMonth+"-"+selectDay+"%");	
				}
			}
			else
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(1, selectYear+"-"+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(1, selectYear+"-"+selectMonth+"-"+selectDay+"%");	
				}
			}
			pst.setInt(2, (page-1)*pageSize);	
			pst.setInt(3, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				empYrMthDaySign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return empYrMthDaySign;
	}

	@Override
	public int findYrMthDaySignTotalRows(String selectYear, String selectMonth, String selectDay) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" WHERE ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			if(Integer.parseInt(selectMonth) < 10)
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(1, selectYear+"-"+0+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(1, selectYear+"-"+0+selectMonth+"-"+selectDay+"%");	
				}
			}
			else
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(1, selectYear+"-"+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(1, selectYear+"-"+selectMonth+"-"+selectDay+"%");	
				}
			}
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public int findDeptYrSignTotalRows(int dep_id, String selectYear) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.dep_id=? AND ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, dep_id);
			pst.setString(2, selectYear+"%");
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public int findDeptYrMthSignTotalRows(int dep_id, String selectYear, String selectMonth) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.dep_id=? AND ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, dep_id);
			if(Integer.parseInt(selectMonth) < 10)
			{
				pst.setString(2, selectYear+"-"+0+selectMonth+"%");
			}
			else
			{
				pst.setString(2, selectYear+"-"+selectMonth+"%");
			}
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public int findDeptYrMthDaySignTotalRows(int dep_id, String selectYear, String selectMonth, String selectDay) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.dep_id=? AND ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, dep_id);	
			if(Integer.parseInt(selectMonth) < 10)
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(2, selectYear+"-"+0+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(2, selectYear+"-"+0+selectMonth+"-"+selectDay+"%");	
				}
			}
			else
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(2, selectYear+"-"+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(2, selectYear+"-"+selectMonth+"-"+selectDay+"%");	
				}
			}
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public JSONArray findYrDeptSign(int dep_id, String selectYear, int page, int pageSize) 
	{
		JSONArray deptSign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.dep_id=? AND ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, dep_id);	
			pst.setString(2, selectYear+"%");	
			pst.setInt(3, (page-1)*pageSize);	
			pst.setInt(4, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				deptSign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return deptSign;
	}

	@Override
	public JSONArray findYrMthDeptSign(int dep_id, String selectYear, String selectMonth, int page, int pageSize) 
	{
		JSONArray deptYrMthSign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.dep_id=? AND ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, dep_id);	
			if(Integer.parseInt(selectMonth) < 10)
			{
				pst.setString(2, selectYear+"-"+0+selectMonth+"%");
			}
			else
			{
				pst.setString(2, selectYear+"-"+selectMonth+"%");
			}
			pst.setInt(3, (page-1)*pageSize);	
			pst.setInt(4, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				deptYrMthSign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return deptYrMthSign;
	}
	@Override
	public JSONArray findYrMthDayDeptSign(int dep_id, String selectYear, String selectMonth, String selectDay, int page,
			int pageSize) 
	{
		JSONArray deptYrMthDaySign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.dep_id=? AND ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, dep_id);	
			if(Integer.parseInt(selectMonth) < 10)
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(2, selectYear+"-"+0+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(2, selectYear+"-"+0+selectMonth+"-"+selectDay+"%");	
				}
			}
			else
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(2, selectYear+"-"+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(2, selectYear+"-"+selectMonth+"-"+selectDay+"%");	
				}
			}
			pst.setInt(3, (page-1)*pageSize);	
			pst.setInt(4, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				deptYrMthDaySign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return deptYrMthDaySign;
	}

	@Override
	public int findEmpYrSignTotalRows(int uid, String selectYear) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" WHERE ems_sign.uid=? AND ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);
			pst.setString(2, selectYear+"%");
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public int findEmpYrMthSignTotalRows(int uid, String selectYear, String selectMonth) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" WHERE ems_sign.uid=? AND ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);
			if(Integer.parseInt(selectMonth) < 10)
			{
				pst.setString(2, selectYear+"-"+0+selectMonth+"%");
			}
			else
			{
				pst.setString(2, selectYear+"-"+selectMonth+"%");
			}
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public int findEmpYrMthDaySignTotalRows(int uid, String selectYear, String selectMonth, String selectDay) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(sid) AS scount");
		sql.append(" FROM ems_sign");
		sql.append(" WHERE ems_sign.uid=? AND ems_sign.startTime LIKE ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);	
			if(Integer.parseInt(selectMonth) < 10)
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(2, selectYear+"-"+0+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(2, selectYear+"-"+0+selectMonth+"-"+selectDay+"%");	
				}
			}
			else
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(2, selectYear+"-"+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(2, selectYear+"-"+selectMonth+"-"+selectDay+"%");	
				}
			}
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("scount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	@Override
	public JSONArray findYrEmpSign(int uid, String selectYear, int page, int pageSize) 
	{
		JSONArray empSign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.uid=? AND ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);	
			pst.setString(2, selectYear+"%");	
			pst.setInt(3, (page-1)*pageSize);	
			pst.setInt(4, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				empSign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return empSign;
	}

	@Override
	public JSONArray fingYrMthEmpSign(int uid, String selectYear, String selectMonth, int page, int pageSize) 
	{
		JSONArray empYrMthSign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.uid=? AND ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);	
			if(Integer.parseInt(selectMonth) < 10)
			{
				pst.setString(2, selectYear+"-"+0+selectMonth+"%");
			}
			else
			{
				pst.setString(2, selectYear+"-"+selectMonth+"%");
			}
			pst.setInt(3, (page-1)*pageSize);	
			pst.setInt(4, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				empYrMthSign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return empYrMthSign;
	}

	@Override
	public JSONArray findYrMthDayEmpSign(int uid, String selectYear, String selectMonth, String selectDay, int page,
			int pageSize) 
	{
		JSONArray empYrMthDaySign = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_sign");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_sign.uid");
		sql.append(" WHERE ems_user.uid=? AND ems_sign.startTime LIKE ?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);	
			if(Integer.parseInt(selectMonth) < 10)
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(2, selectYear+"-"+0+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(2, selectYear+"-"+0+selectMonth+"-"+selectDay+"%");	
				}
			}
			else
			{
				if(Integer.parseInt(selectDay) < 10)
				{
					pst.setString(2, selectYear+"-"+selectMonth+"-"+0+selectDay+"%");	
				}
				else
				{
					pst.setString(2, selectYear+"-"+selectMonth+"-"+selectDay+"%");	
				}
			}
			pst.setInt(3, (page-1)*pageSize);	
			pst.setInt(4, pageSize);	
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("sid", rs.getInt("sid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("startTime", rs.getString("startTime"));
				jo.put("endTime", rs.getString("endTime"));
				jo.put("state", rs.getString("state"));
				jo.put("uid", rs.getInt("uid"));
				empYrMthDaySign.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return empYrMthDaySign;
	}

}
